package jsyf.oa.dao.report.newAppReport;

import com.alibaba.fastjson.JSONObject;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.util.List;


@SuppressWarnings("all")
@Repository
public class NewAppReportForGroupDao extends BaseDao {

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return null;
    }

    public List<JSONObject> getRTLEmailForZSQ(String senderIds, String receiverIds, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "sysRegion.regionName,\n" +
                "sysRegion.regionId,\n" +
                "\ta.`NAME` AS readerName,\n" +
                "\ta.`ID` AS readerId,\n" +
                "\tb.`NAME` AS userName,\n" +
                "\tb.`ID` AS userId,\n" +
                "\temail.WId,\n" +
                "\temail.emailId,\n" +
                "\tCASE\n" +
                "WHEN email.READER = '1' THEN\n" +
                "\t'已读'\n" +
                "ELSE\n" +
                "\t'未读'\n" +
                "END AS reader,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\temail.TITLE IS NOT NULL || email.TITLE != ''\n" +
                ") THEN\n" +
                "\t'已发送'\n" +
                "ELSE\n" +
                "\t'未发送'\n" +
                "END AS titleSend,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\tt_sys_leave.id IS NOT NULL \n" +
                ") THEN\n" +
                "\t'请假'\n" +
                "WHEN (\n" +
                "\tt_sys_sign_in.id IS  NULL \n" +
                ") THEN\n" +
                "\t'缺勤'\n" +
                "ELSE\n" +
                "\t'出勤'\n" +
                "END AS leaveDate,\n" +
                " IFNULL(emailNum.replyNum, '0') AS replyNum\n" +
                "FROM\n" +
                "(\n" +
                "SELECT t_sys_user.ID\n" +
                "FROM t_sys_user\n" +
                "WHERE ID  IN (" + senderIds + "))as sysUser \n" +
                "LEFT JOIN\n(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_email.CREATE_USER_ID AS senderId,\n" +
                "\t\t\tt_sys_email_receiver.USER_ID AS receiverId,\n" +
                "\t\t\tt_wrt_main.ID AS id,\n" +
                "\t\t\tt_sys_email_receiver.READER,\n" +
                "\t\t\tt_wrt_main.TITLE,\n" +
                "\t\t\tt_sys_email.ID AS emailId,\n" +
                "\t\t\tt_sys_email.DATA_ID AS WId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_email_receiver\n" +
                "\t\tLEFT JOIN t_sys_email ON t_sys_email_receiver.EMAIL_ID = t_sys_email.ID\n" +
                "\t\tLEFT JOIN t_wrt_main ON t_wrt_main.ID = t_sys_email.DATA_ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_wrt_main.flage !=- 1\n" +
                "\t\tAND t_sys_email.E_TYPE = 1\n" +
                "\t\tAND t_sys_email_receiver.USER_ID IN (" + receiverIds + ")\n" +
                "\t\tAND t_sys_email.CREATE_USER_ID IN (" + senderIds + ")\n" +
                "\t\tAND t_sys_email.REPORT_DATE = '" + dateTime + "'\n" +
                "\t) AS email ON sysUser.id=email.senderId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tCOUNT(1) AS replyNum,\n" +
                "\t\tDATA_ID\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND REPLY_TIME IS NOT NULL\n" +
                "\tAND DATA_ID IN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_wrt_main.ID\n" +
                "\t\tFROM\n" +
                "\t\t\tt_wrt_main\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_wrt_main.USER_ID IN (" + senderIds + ")\n" +
                "\t\t AND t_sys_email_receiver.USER_ID IN (" + receiverIds + ") \n" +
                "\t\tAND t_wrt_main.REPORT_DATE = '" + dateTime + "'\n" +
                "\t)\n" +
                "\tGROUP BY\n" +
                "\t\tDATA_ID\n" +
                ") AS emailNum ON email.WId = emailNum.DATA_ID\n" +
                "LEFT JOIN t_sys_leave ON sysUser.id = t_sys_leave.USER_ID\n" +
                "AND LEAVE_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_sign_in ON sysUser.userId = t_sys_sign_in.USER_ID\n" +
                "AND SIGN_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_user AS a ON a.ID = email.receiverId\n" +
                "LEFT JOIN t_sys_user AS b ON b.ID = sysUser.ID\n" +
                "\tLEFT JOIN  (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\t\tt_sys_region.`ID` AS regionId,\n" +
                "\t\t\tt_sys_region.SORT_NO AS regionSortNo,\n" +
                "\t\t\tt_sys_company.SORT_NO AS companySortNo,\n" +
                "\t\t\tt_sys_region.COMPANY_ID AS companyId,\n" +
                "\t\t\tt_sys_user_2_region.USER_ID AS userId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user_2_region\n" +
                "\t\tLEFT JOIN t_sys_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_region.company_id = t_sys_company.id\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_region.PARENT_ID = 0\n" +
                "\t) AS sysRegion on sysRegion.userId=sysUser.id\n" +
                "ORDER BY\n" +
                "\tsysRegion.companySortNo,sysRegion.regionSortNo,email.receiverId,email.senderId\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getSignForGroupTop(String companyId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tmanager.managerId,\n" +
                "\tmanager.managerName,\n" +
                "\tmanager.companyName,\n" +
                "\tsign_leave.createTime,\n" +
                "\tIFNULL(sign_leave.signState, 2) AS signState,\n" +
                "\tsign_leave.address\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tid AS managerId,\n" +
                "\t\t\t`name` AS managerName,\n" +
                "\t\t\tcompanyId,\n" +
                "\t\t\tcompanyName,\n" +
                "\t\t\tcompanySortNo,\n" +
                "\t\t\tdepartSortNo,\n" +
                "\t\t\troleSortNo,\n" +
                "\t\t\tuserSortNo\n" +
                "\t\tFROM\n" +
                "\t\t\tv_sys_user\n" +
                "\t\tWHERE\n" +
                "\t\t\tcompanyId = '" + companyId + "'\n" +
                "\t\tAND jobState != 1\n" +
                "\t\tAND roleName = '总经理'\n" +
                "\t) AS manager\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_user.ID AS managerId,\n" +
                "\t\tt_sys_user.`NAME` AS managerName,\n" +
                "\t\tt_sys_sign_in.CREATE_TIME AS createTime,\n" +
                "\t\tt_sys_sign_in.ADDRESS AS address,\n" +
                "\t\t'0' AS signState\n" +
                "\tFROM\n" +
                "\t\tt_sys_sign_in\n" +
                "\tLEFT JOIN t_sys_user ON t_sys_sign_in.USER_ID = t_sys_user.ID\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE\n" +
                "\t\tt_sys_sign_in.SIGN_DATE = '" + dateTime + "'\n" +
                "\tAND t_sys_role_name.`NAME` = '总经理'\n" +
                "\tUNION ALL\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user.ID AS managerId,\n" +
                "\t\t\tt_sys_user.`NAME` AS managerName,\n" +
                "\t\t\tt_sys_leave.CREATE_TIME AS createTime,\n" +
                "\t\t\tt_sys_leave.REMARK AS address,\n" +
                "\t\t\t'1' AS signState\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_leave\n" +
                "\t\tLEFT JOIN t_sys_user ON t_sys_leave.USER_ID = t_sys_user.ID\n" +
                "\t\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\t\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_leave.LEAVE_DATE = '" + dateTime + "'\n" +
                "\t\tAND t_sys_role_name.`NAME` = '总经理'\n" +
                ") AS sign_leave ON manager.managerId = sign_leave.managerId\n" +
                "ORDER BY\n" +
                "\tmanager.companySortNo,\n" +
                "\tmanager.departSortNo,\n" +
                "\tmanager.roleSortNo,\n" +
                "\tmanager.userSortNo");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getSignForGroup(String companyId, String dateTime, String roleName) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tregion.regionName,\n" +
                "\tmanager.managerId,\n" +
                "\tmanager.managerName,\n" +
                "\tsign_leave.createTime,\n" +
                "\tIFNULL(sign_leave.signState, 2) AS signState,\n" +
                "\tsign_leave.address\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_region.ID AS regionId,\n" +
                "\t\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\t\tt_sys_region.SORT_NO AS sortNo\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_region\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_sys_region.COMPANY_ID = '" + companyId + "'\n" +
                "\t\tAND t_sys_region.PARENT_ID = '0'\n" +
                "\t\tAND t_sys_region.DEL_FLAG = '0'\n" +
                "\t) AS region\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_user.ID AS managerId,\n" +
                "\t\tt_sys_user.`NAME` AS managerName,\n" +
                "\t\tt_sys_user_2_region.REGION_ID AS regionId\n" +
                "\tFROM\n" +
                "\t\tt_sys_user\n" +
                "\tLEFT JOIN t_sys_user_2_region ON t_sys_user.ID = t_sys_user_2_region.USER_ID\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE\n" +
                "\t\tt_sys_role_name.`NAME` = '" + roleName + "'\n" +
                "\tAND t_sys_user.JOB_STATE != 1\n" +
                ") AS manager ON manager.regionId = region.regionId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_user.ID AS managerId,\n" +
                "\t\tt_sys_user.`NAME` AS managerName,\n" +
                "\t\tt_sys_user_2_region.REGION_ID AS regionId,\n" +
                "\t\tt_sys_sign_in.CREATE_TIME AS createTime,\n" +
                "\t\tt_sys_sign_in.ADDRESS AS address,\n" +
                "\t\t'0' AS signState\n" +
                "\tFROM\n" +
                "\t\tt_sys_sign_in\n" +
                "\tLEFT JOIN t_sys_user_2_region ON t_sys_sign_in.USER_ID = t_sys_user_2_region.USER_ID\n" +
                "\tLEFT JOIN t_sys_user ON t_sys_sign_in.USER_ID = t_sys_user.ID\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE\n" +
                "\t\tt_sys_sign_in.SIGN_DATE = '" + dateTime + "'\n" +
                "\tAND t_sys_role_name.`NAME` = '" + roleName + "'\n" +
                "\tUNION ALL\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user.ID AS managerId,\n" +
                "\t\t\tt_sys_user.`NAME` AS managerName,\n" +
                "\t\t\tt_sys_user_2_region.REGION_ID AS regionId,\n" +
                "\t\t\tt_sys_leave.CREATE_TIME AS createTime,\n" +
                "\t\t\tt_sys_leave.REMARK AS address,\n" +
                "\t\t\t'1' AS signState\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_leave\n" +
                "\t\tLEFT JOIN t_sys_user_2_region ON t_sys_leave.USER_ID = t_sys_user_2_region.USER_ID\n" +
                "\t\tLEFT JOIN t_sys_user ON t_sys_leave.USER_ID = t_sys_user.ID\n" +
                "\t\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\t\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_leave.LEAVE_DATE = '" + dateTime + "'\n" +
                "\t\tAND t_sys_role_name.`NAME` = '" + roleName + "'\n" +
                ") AS sign_leave ON region.regionId = sign_leave.regionId\n" +
                "ORDER BY\n" +
                "\tregion.sortNo");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getRegionManagerByUserId(String userId, String dateTime, StringBuffer condition) {
        StringBuffer sql = new StringBuffer();
        if (null == condition) {
            condition = new StringBuffer();
            condition.append(" AND roleName in ('区域经理','区域组长','大区经理')\n");
        }
        sql.append("SELECT v_sys_user.id as userId\n" +
                "FROM v_sys_user\n" +
                "LEFT JOIN t_sys_user_leave ON t_sys_user_leave.USER_ID = v_sys_user.id\n" +
                "WHERE 1=1\n");
        sql.append(condition);
        sql.append("\tAND v_sys_user.id in (" + userId + ")\n" +
                "AND (v_sys_user.jobState!=1 OR t_sys_user_leave.LEAVE_DATE='" + dateTime + "')\n\n" +
                "GROUP BY v_sys_user.id\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getUserIdByRegionId(String regionId, String roleName, String userIds) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tv_sys_user.id AS userId\n" +
                "FROM\n" +
                "\tt_sys_user_2_region\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=t_sys_user_2_region.REGION_ID\n" +
                "LEFT JOIN v_sys_user ON v_sys_user.ID=t_sys_user_2_region.USER_ID\n" +
                "\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_user_2_region.REGION_ID = '" + regionId + "'\n" +
                "AND v_sys_user.roleName='" + roleName + "'\n" +
                "AND v_sys_user.ID IN (" + userIds + ")\n" +
                "GROUP BY v_sys_user.id\n");
        return super.queryForJsonList(sql.toString());
    }

/*    public List<JSONObject> getUserIdByRegionIdForPartTimeJob(String regionId, String roleName, String userIds, Boolean flag) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tv_sys_user.id AS userId\n" +
                "FROM\n" +
                "\tt_sys_user_2_region\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=t_sys_user_2_region.REGION_ID\n" +
                "LEFT JOIN v_sys_user ON v_sys_user.ID=t_sys_user_2_region.USER_ID AND t_sys_region.`NAME`=v_sys_user.departName\n" +
                "\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_user_2_region.REGION_ID = '" + regionId + "'\n" +
                "AND v_sys_user.roleName in (" + roleName + ")\n" +
                "AND v_sys_user.ID IN (" + userIds + ")\n");
        if (flag) {
            sql.append("and v_sys_user.main =1 \n");
        }
        sql.append("GROUP BY v_sys_user.id\n");
        return super.queryForJsonList(sql.toString());
    }*/


    //大区经理工作报告（区域主表）
    public List<JSONObject> getBRMEmailForGroup(String senderIds, String receiverIds, String companyId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\t*\n" +
                "FROM\n" +
                "\t(\n" +
                "SELECT\n" +
                "sysRegion.regionName,\n" +
                "sysRegion.companySortNo,\n" +
                "sysRegion.regionSortNo,\n" +
                "sysRegion.regionId,\n" +
                "sysRegion.companyId,\n" +
                "\tsysUser.userId,\n" +
                "\ta.`NAME` AS readerName,\n" +
                "\t\t\ta.`ID` AS readerId,\n" +
                "\t\t\tb.PARENT_ID AS senderParentId,\n" +
                "\tsysUser.userName,\n" +
                "\temail.WId,\n" +
                "\temail.emailId,\n" +
                "\tCASE\n" +
                "WHEN email.READER = '1' THEN\n" +
                "\t'已读'\n" +
                "ELSE\n" +
                "\t'未读'\n" +
                "END AS reader,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\temail.TITLE IS NOT NULL || email.TITLE != ''\n" +
                ") THEN\n" +
                "\t'已发送'\n" +
                "ELSE\n" +
                "\t'未发送'\n" +
                "END AS titleSend,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\tt_sys_leave.id IS NOT NULL \n" +
                ") THEN\n" +
                "\t'请假'\n" +
                "WHEN (\n" +
                "\tt_sys_sign_in.id IS  NULL \n" +
                ") THEN\n" +
                "\t'缺勤'\n" +
                "ELSE\n" +
                "\t'出勤'\n" +
                "END AS leaveDate,\n" +
                " IFNULL(emailNum.replyNum, '0') AS replyNum\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\t\tt_sys_region.`ID` AS regionId,\n" +
                "\t\t\tt_sys_region.SORT_NO AS regionSortNo,\n" +
                "\t\t\tt_sys_company.SORT_NO AS companySortNo,\n" +
                "\t\t\tt_sys_region.COMPANY_ID AS companyId\n" +
                "\t\tFROM\n" +
                "t_sys_region \n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_region.COMPANY_ID\n" +
                "\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_region.COMPANY_ID = '" + companyId + "'\n" +
                "\t\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_region.PARENT_ID = 0\n" +
                "\t) AS sysRegion\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_user.ID AS userId,\n" +
                "\t\tt_sys_depart.`NAME` AS departName,\n" +
                "\t\tt_sys_user.`NAME` AS userName,\n" +
                "\t\tt_sys_company.id AS companyId\n" +
                "\tFROM\n" +
                "\t\tt_sys_user\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_depart.ID = t_sys_role.DEPART_ID\n" +
                "\tLEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_depart.COMPANY_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\tAND t_sys_depart.DEL_FLAG = 0\n" +
                "\tAND t_sys_role.DEL_FLAG = 0\n" +
                "\tAND t_sys_user.ID IN (" + senderIds + ")\n" +
                ") AS sysUser ON sysUser.departName = sysRegion.regionName\n" +
                "AND sysRegion.companyId = sysUser.companyId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_email.CREATE_USER_ID AS senderId,\n" +
                "\t\tt_sys_email_receiver.USER_ID AS receiverId,\n" +
                "\t\tt_wrt_main.ID AS id,\n" +
                "\t\tt_sys_email_receiver.READER,\n" +
                "\t\tt_wrt_main.TITLE,\n" +
                "\t\tt_sys_email.ID AS emailId,\n" +
                "\t\tt_sys_email.DATA_ID AS WId\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tLEFT JOIN t_sys_email ON t_sys_email_receiver.EMAIL_ID = t_sys_email.ID\n" +
                "\tLEFT JOIN t_wrt_main ON t_wrt_main.ID = t_sys_email.DATA_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_wrt_main.flage !=- 1\n" +
                "\tAND t_sys_email.E_TYPE = 1\n" +
                "\tAND t_sys_email_receiver.USER_ID IN (" + receiverIds + ")\n" +
                "\tAND t_sys_email.CREATE_USER_ID IN (" + senderIds + ")\n" +
                "\tAND t_sys_email.REPORT_DATE = '" + dateTime + "'\n" +
                ") AS email ON email.senderId = sysUser.userId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tCOUNT(1) AS replyNum,\n" +
                "\t\tDATA_ID\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND REPLY_TIME IS NOT NULL\n" +
                "\tAND DATA_ID IN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_wrt_main.ID\n" +
                "\t\tFROM\n" +
                "\t\t\tt_wrt_main\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_wrt_main.USER_ID IN (" + senderIds + ")\n" +
                "\t\tAND t_wrt_main.REPORT_DATE = '" + dateTime + "'\n" +
                "\t\t AND t_sys_email_receiver.USER_ID IN (" + receiverIds + ") \n" +
                "\t)\n" +
                "\tGROUP BY\n" +
                "\t\tDATA_ID\n" +
                ") AS emailNum ON email.WId = emailNum.DATA_ID\n" +
                "LEFT JOIN t_sys_leave ON sysUser.userId = t_sys_leave.USER_ID\n" +
                "AND LEAVE_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_sign_in ON sysUser.userId = t_sys_sign_in.USER_ID\n" +
                "AND SIGN_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_user AS a ON a.ID = email.receiverId\n" +
                "LEFT JOIN t_sys_user AS b ON b.ID = sysUser.userId\n" +
                "ORDER BY\n" +
                "\tsysRegion.companySortNo,\n" +
                "\tsysRegion.regionSortNo,\n" +
                "\temail.receiverId\n" +
                "\t) AS obj\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND (\n" +
                "\tobj.senderParentId = obj.readerId\n" +
                "\tOR obj.readerId IS NULL\n" +
                ")\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getRegionIdByCompanyIdForGroup(String companyId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_sys_company.SORT_NO AS companySortNo,\n" +
                "\tt_sys_region.SORT_NO AS regionSortNo,\n" +
                "\tt_sys_region.ID AS regionId,\n" +
                "\tt_sys_region.`NAME` AS regionName\n" +
                "FROM\n" +
                "\tt_sys_region\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_region.COMPANY_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_region.DEL_FLAG = 0\n" +
                "AND t_sys_company.DEL_FLAG = 0\n" +
                "AND t_sys_region.PARENT_ID = 0\n" +
                "AND t_sys_region.COMPANY_ID = '" + companyId + "'\n" +
                "ORDER BY\n" +
                "\tcompanySortNo,\n" +
                "\tregionSortNo\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getRmIdByRegionId(String regionId, String roleName, String companyId, String dateTime, Boolean flag) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tv_sys_user.id AS userId\n" +
                "FROM\n" +
                "\tt_sys_user_2_region\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=t_sys_user_2_region.REGION_ID\n" +
                "LEFT JOIN v_sys_user ON v_sys_user.ID=t_sys_user_2_region.USER_ID  AND t_sys_region.NAME=v_sys_user.departName\n" +
                "LEFT JOIN t_sys_user_leave ON v_sys_user.ID=t_sys_user_leave.user_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND (v_sys_user.jobState!=1\n");
        if (null != dateTime) {
            sql.append("or t_sys_user_leave.LEAVE_DATE='" + dateTime + "'\n");
        }
        sql.append(") AND t_sys_region.DEL_FLAG = 0\n");
        if (flag) {
            sql.append("and v_sys_user.main = 1 \n");
        }
        sql.append("AND t_sys_user_2_region.REGION_ID = '" + regionId + "'\n" +
                "AND v_sys_user.roleName in (" + roleName + ")\n" +
                "AND v_sys_user.companyId = '" + companyId + "'\n" +
                "GROUP BY v_sys_user.id\n");
        return super.queryForJsonList(sql.toString());
    }

    //人做主表
    public List<JSONObject> getUserRegionEmailForGroup(String senderIds, String receiverIds, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "sysRegion.regionName,\n" +
                "sysRegion.companyId,\n" +
                "sysRegion.companySortNo,\n" +
                "sysRegion.regionSortNo,\n" +
                "sysRegion.regionId,\n" +
                "\tsysUser.userId,\n" +
                "\ta.`NAME` AS readerName,\n" +
                "\t\t\ta.`ID` AS readerId,\n" +
                "\t\t\tb.PARENT_ID AS senderParentId,\n" +
                "\tsysUser.userName,\n" +
                "\temail.WId,\n" +
                "\temail.emailId,\n" +
                "\tCASE\n" +
                "WHEN email.READER = '1' THEN\n" +
                "\t'已读'\n" +
                "ELSE\n" +
                "\t'未读'\n" +
                "END AS reader,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\temail.TITLE IS NOT NULL || email.TITLE != ''\n" +
                ") THEN\n" +
                "\t'已发送'\n" +
                "ELSE\n" +
                "\t'未发送'\n" +
                "END AS titleSend,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\tt_sys_leave.id IS NOT NULL \n" +
                ") THEN\n" +
                "\t'请假'\n" +
                "WHEN (\n" +
                "\tt_sys_sign_in.id IS  NULL \n" +
                ") THEN\n" +
                "\t'缺勤'\n" +
                "ELSE\n" +
                "\t'出勤'\n" +
                "END AS leaveDate,\n" +
                " IFNULL(emailNum.replyNum, '0') AS replyNum\n" +
                "FROM\n" +
                "(\n" +
                "\tSELECT\n" +
                "\t\tt_sys_user.ID AS userId,\n" +
                "\t\tt_sys_depart.`NAME` AS departName,\n" +
                "\t\tt_sys_user.`NAME` AS userName,\n" +
                "\t\tt_sys_company.id AS companyId\n" +
                "\tFROM\n" +
                "\t\tt_sys_user\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_depart.ID = t_sys_role.DEPART_ID\n" +
                "\tLEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_depart.COMPANY_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\tAND t_sys_depart.DEL_FLAG = 0\n" +
                "\tAND t_sys_role.DEL_FLAG = 0\n" +
                "\tAND t_sys_user.ID IN (" + senderIds + ")\n" +
                ") AS sysUser \n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_email.CREATE_USER_ID AS senderId,\n" +
                "\t\tt_sys_email_receiver.USER_ID AS receiverId,\n" +
                "\t\tt_wrt_main.ID AS id,\n" +
                "\t\tt_sys_email_receiver.READER,\n" +
                "\t\tt_wrt_main.TITLE,\n" +
                "\t\tt_sys_email.ID AS emailId,\n" +
                "\t\tt_sys_email.DATA_ID AS WId\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tLEFT JOIN t_sys_email ON t_sys_email_receiver.EMAIL_ID = t_sys_email.ID\n" +
                "\tLEFT JOIN t_wrt_main ON t_wrt_main.ID = t_sys_email.DATA_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_wrt_main.flage !=- 1\n" +
                "\tAND t_sys_email.E_TYPE = 1\n" +
                "\tAND t_sys_email_receiver.USER_ID IN (" + receiverIds + ")\n" +
                "\tAND t_sys_email.CREATE_USER_ID IN (" + senderIds + ")\n" +
                "\tAND t_sys_email.REPORT_DATE = '" + dateTime + "'\n" +
                ") AS email ON email.senderId = sysUser.userId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tCOUNT(1) AS replyNum,\n" +
                "\t\tDATA_ID\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND REPLY_TIME IS NOT NULL\n" +
                "\tAND DATA_ID IN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_wrt_main.ID\n" +
                "\t\tFROM\n" +
                "\t\t\tt_wrt_main\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_wrt_main.USER_ID IN (" + senderIds + ")\n" +
                "\t\tAND t_wrt_main.REPORT_DATE = '" + dateTime + "'\n" +
                "\t\t AND t_sys_email_receiver.USER_ID IN (" + receiverIds + ") \n" +
                "\t)\n" +
                "\tGROUP BY\n" +
                "\t\tDATA_ID\n" +
                ") AS emailNum ON email.WId = emailNum.DATA_ID\n" +
                "LEFT JOIN t_sys_leave ON sysUser.userId = t_sys_leave.USER_ID\n" +
                "AND LEAVE_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_sign_in ON sysUser.userId = t_sys_sign_in.USER_ID\n" +
                "AND SIGN_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_user AS a ON a.ID = email.receiverId\n" +
                "LEFT JOIN t_sys_user AS b ON b.ID = sysUser.userId\n" +
                "\tLEFT JOIN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\t\tt_sys_region.`ID` AS regionId,\n" +
                "\t\t\tt_sys_user_2_region.user_id AS userId,\n" +
                "\t\t\tt_sys_region.SORT_NO AS regionSortNo,\n" +
                "\t\t\tt_sys_company.SORT_NO AS companySortNo,\n" +
                "\t\t\tt_sys_region.COMPANY_ID AS companyId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user_2_region\n" +
                "\t\tLEFT JOIN t_sys_region ON t_sys_region.id = t_sys_user_2_region.REGION_ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_region.COMPANY_ID\n" +
                "\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t\tWHERE 1=1 \n" +
                "\t\tAND t_sys_user_2_region.user_id in (" + senderIds + ")\n" +
                "\t\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_region.PARENT_ID = 0\n" +
                "\t) AS sysRegion on sysUser.userId=sysRegion.userId\n" +
                "ORDER BY\n" +
                "\tsysRegion.companySortNo,\n" +
                "\tsysRegion.regionSortNo,\n" +
                "\temail.receiverId\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getZJLEmailForGroup(String senderIds, String receiverIds, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "sysRegion.regionName,\n" +
                "sysRegion.companyName,\n" +
                "sysRegion.regionId,\n" +
                "\ta.`NAME` AS readerName,\n" +
                "\ta.`ID` AS readerId,\n" +
                "\tb.`NAME` AS userName,\n" +
                "\tb.`ID` AS userId,\n" +
                "\temail.WId,\n" +
                "\temail.emailId,\n" +
                "\tCASE\n" +
                "WHEN email.READER = '1' THEN\n" +
                "\t'已读'\n" +
                "ELSE\n" +
                "\t'未读'\n" +
                "END AS reader,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\temail.TITLE IS NOT NULL || email.TITLE != ''\n" +
                ") THEN\n" +
                "\t'已发送'\n" +
                "ELSE\n" +
                "\t'未发送'\n" +
                "END AS titleSend,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\tt_sys_leave.id IS NOT NULL \n" +
                ") THEN\n" +
                "\t'请假'\n" +
                "WHEN (\n" +
                "\tt_sys_sign_in.id IS  NULL \n" +
                ") THEN\n" +
                "\t'缺勤'\n" +
                "ELSE\n" +
                "\t'出勤'\n" +
                "END AS leaveDate,\n" +
                " IFNULL(emailNum.replyNum, '0') AS replyNum\n" +
                "FROM\n" +
                "(\n" +
                "SELECT t_sys_user.ID\n" +
                "FROM t_sys_user\n" +
                "WHERE ID  IN (" + senderIds + "))as sysUser \n" +
                "LEFT JOIN\n(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_email.CREATE_USER_ID AS senderId,\n" +
                "\t\t\tt_sys_email_receiver.USER_ID AS receiverId,\n" +
                "\t\t\tt_wrt_main.ID AS id,\n" +
                "\t\t\tt_sys_email_receiver.READER,\n" +
                "\t\t\tt_wrt_main.TITLE,\n" +
                "\t\t\tt_sys_email.ID AS emailId,\n" +
                "\t\t\tt_sys_email.DATA_ID AS WId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_email_receiver\n" +
                "\t\tLEFT JOIN t_sys_email ON t_sys_email_receiver.EMAIL_ID = t_sys_email.ID\n" +
                "\t\tLEFT JOIN t_wrt_main ON t_wrt_main.ID = t_sys_email.DATA_ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_wrt_main.flage !=- 1\n" +
                "\t\tAND t_sys_email.E_TYPE = 1\n" +
                "\t\tAND t_sys_email_receiver.USER_ID IN (" + receiverIds + ")\n" +
                "\t\tAND t_sys_email.CREATE_USER_ID IN (" + senderIds + ")\n" +
                "\t\tAND t_sys_email.REPORT_DATE = '" + dateTime + "'\n" +
                "\t) AS email ON sysUser.id=email.senderId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tCOUNT(1) AS replyNum,\n" +
                "\t\tDATA_ID\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND REPLY_TIME IS NOT NULL\n" +
                "\tAND DATA_ID IN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_wrt_main.ID\n" +
                "\t\tFROM\n" +
                "\t\t\tt_wrt_main\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_wrt_main.USER_ID IN (" + senderIds + ")\n" +
                "\t\t AND t_sys_email_receiver.USER_ID IN (" + receiverIds + ") \n" +
                "\t\tAND t_wrt_main.REPORT_DATE = '" + dateTime + "'\n" +
                "\t)\n" +
                "\tGROUP BY\n" +
                "\t\tDATA_ID\n" +
                ") AS emailNum ON email.WId = emailNum.DATA_ID\n" +
                "LEFT JOIN t_sys_leave ON sysUser.id = t_sys_leave.USER_ID\n" +
                "AND LEAVE_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_sign_in ON sysUser.id = t_sys_sign_in.USER_ID\n" +
                "AND SIGN_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_user AS a ON a.ID = email.receiverId\n" +
                "LEFT JOIN t_sys_user AS b ON b.ID = sysUser.ID\n" +
                "\tLEFT JOIN  (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\t\tt_sys_region.`ID` AS regionId,\n" +
                "\t\t\tt_sys_region.SORT_NO AS regionSortNo,\n" +
                "\t\t\tt_sys_company.SORT_NO AS companySortNo,\n" +
                "\t\t\tt_sys_company.NAME AS companyName,\n" +
                "\t\t\tt_sys_region.COMPANY_ID AS companyId,\n" +
                "\t\t\tt_sys_user_2_region.USER_ID AS userId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user_2_region\n" +
                "\t\tLEFT JOIN t_sys_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_region.company_id = t_sys_company.id\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_region.PARENT_ID = 0\n" +
                "\t) AS sysRegion on sysRegion.userId=sysUser.id\n" +
                "ORDER BY\n" +
                "\tsysRegion.companySortNo,sysRegion.regionSortNo,email.receiverId,email.senderId\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getSDEmailForGroup(String senderIds, String receiverIds, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "sysRegion.regionName,\n" +
                "sysRegion.regionId,\n" +
                "\ta.`NAME` AS readerName,\n" +
                "\ta.`ID` AS readerId,\n" +
                "\tb.`NAME` AS userName,\n" +
                "\tb.`ID` AS userId,\n" +
                "\temail.WId,\n" +
                "\temail.emailId,\n" +
                "\tCASE\n" +
                "WHEN email.READER = '1' THEN\n" +
                "\t'已读'\n" +
                "ELSE\n" +
                "\t'未读'\n" +
                "END AS reader,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\temail.TITLE IS NOT NULL || email.TITLE != ''\n" +
                ") THEN\n" +
                "\t'已发送'\n" +
                "ELSE\n" +
                "\t'未发送'\n" +
                "END AS titleSend,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\tt_sys_leave.LEAVE_DATE IS NOT NULL || t_sys_leave.LEAVE_DATE != ''\n" +
                ") THEN\n" +
                "\t'请假'\n" +
                "ELSE\n" +
                "\t'未请假'\n" +
                "END AS leaveDate,\n" +
                " IFNULL(emailNum.replyNum, '0') AS replyNum\n" +
                "FROM\n" +
                "(\n" +
                "SELECT t_sys_user.ID\n" +
                "FROM t_sys_user\n" +
                "WHERE ID  IN (" + senderIds + "))as sysUser \n" +
                "LEFT JOIN\n(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_email.CREATE_USER_ID AS senderId,\n" +
                "\t\t\tt_sys_email_receiver.USER_ID AS receiverId,\n" +
                "\t\t\tt_wrt_main.ID AS id,\n" +
                "\t\t\tt_sys_email_receiver.READER,\n" +
                "\t\t\tt_wrt_main.TITLE,\n" +
                "\t\t\tt_sys_email.ID AS emailId,\n" +
                "\t\t\tt_sys_email.DATA_ID AS WId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_email_receiver\n" +
                "\t\tLEFT JOIN t_sys_email ON t_sys_email_receiver.EMAIL_ID = t_sys_email.ID\n" +
                "\t\tLEFT JOIN t_wrt_main ON t_wrt_main.ID = t_sys_email.DATA_ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_wrt_main.flage !=- 1\n" +
                "\t\tAND t_sys_email.E_TYPE = 1\n" +
                "\t\tAND t_sys_email_receiver.USER_ID IN (" + receiverIds + ")\n" +
                "\t\tAND t_sys_email.CREATE_USER_ID IN (" + senderIds + ")\n" +
                "\t\tAND t_sys_email.REPORT_DATE = '" + dateTime + "'\n" +
                "\t) AS email  ON sysUser.id=email.senderId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tCOUNT(1) AS replyNum,\n" +
                "\t\tDATA_ID\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND REPLY_TIME IS NOT NULL\n" +
                "\tAND DATA_ID IN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_wrt_main.ID\n" +
                "\t\tFROM\n" +
                "\t\t\tt_wrt_main\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_wrt_main.USER_ID IN (" + senderIds + ")\n" +
                "\t\t AND t_sys_email_receiver.USER_ID IN (" + receiverIds + ") \n" +
                "\t\tAND t_wrt_main.REPORT_DATE = '" + dateTime + "'\n" +
                "\t)\n" +
                "\tGROUP BY\n" +
                "\t\tDATA_ID\n" +
                ") AS emailNum ON email.WId = emailNum.DATA_ID\n" +
                "LEFT JOIN t_sys_leave ON sysUser.id = t_sys_leave.USER_ID\n" +
                "AND LEAVE_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_user AS a ON a.ID = email.receiverId\n" +
                "LEFT JOIN t_sys_user AS b ON b.ID = email.senderId\n" +
                "\tLEFT JOIN  (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\t\tt_sys_region.`ID` AS regionId,\n" +
                "\t\t\tt_sys_region.SORT_NO AS regionSortNo,\n" +
                "\t\t\tt_sys_company.SORT_NO AS companySortNo,\n" +
                "\t\t\tt_sys_region.COMPANY_ID AS companyId,\n" +
                "\t\t\tt_sys_user_2_region.USER_ID AS userId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user_2_region\n" +
                "\t\tLEFT JOIN t_sys_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_region.company_id = t_sys_company.id\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_sys_user_2_region.USER_ID IN (" + senderIds + ")\n" +
                "\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_region.PARENT_ID = 0\n" +
                "\t) AS sysRegion on sysRegion.userId=sysUser.id\n" +
                "ORDER BY\n" +
                "\tsysRegion.companySortNo,sysRegion.regionSortNo,email.receiverId,email.senderId\n");
        return super.queryForJsonList(sql.toString());
    }

    //区域经理（区域主表）
    public List<JSONObject> getEmailByUserIdAndRoleNameForRM(String senderIds, String receiverIds, String dateTime, String regionId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "region.regionName,\n" +
                "\tregion.regionId,\n" +
                "\t\tregion.companyId,\n" +
                "\tsysRegion.userId,\n" +
                "\tt_sys_user.`NAME` AS readerName,\n" +
                "\tt_sys_user.`ID` AS readerId,\n" +
                "\tsysRegion.userName,\n" +
                "\temail.WId,\n" +
                "\temail.emailId,\n" +
                "\tCASE\n" +
                "WHEN email.READER = '1' THEN\n" +
                "\t'已读'\n" +
                "ELSE\n" +
                "\t'未读'\n" +
                "END AS reader,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\temail.TITLE IS NOT NULL || email.TITLE != ''\n" +
                ") THEN\n" +
                "\t'已发送'\n" +
                "ELSE\n" +
                "\t'未发送'\n" +
                "END AS titleSend,\n" +
                " CASE\n" +
                "WHEN (\n" +
                "\tt_sys_leave.id IS NOT NULL \n" +
                ") THEN\n" +
                "\t'请假'\n" +
                "WHEN (\n" +
                "\tt_sys_sign_in.id IS  NULL \n" +
                ") THEN\n" +
                "\t'缺勤'\n" +
                "ELSE\n" +
                "\t'出勤'\n" +
                "END AS leaveDate,\n" +
                " IFNULL(emailNum.replyNum, '0') AS replyNum\n" +
                "FROM" +
                "(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\t\tt_sys_region.`ID` AS regionId,\n" +
                "\t\t\tt_sys_region.COMPANY_ID AS companyId,\n" +
                "t_sys_region.SORT_NO AS regionSortNo,\n" +
                "\t\t\tt_sys_company.SORT_NO AS companySortNo\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_region\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_region.COMPANY_ID\n" +
                "\t\tWHERE 1=1\n" +
                "\t\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_region.PARENT_ID = '" + regionId + "'\n" +
                "\t) AS region\n" +
                "\tLEFT JOIN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user_2_region.USER_ID AS userId,\n" +
                "\t\t\tt_sys_user.`NAME` AS userName,\n" +
                "\t\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\t\tt_sys_region.`ID` AS regionId,\n" +
                "\t\t\tt_sys_region.SORT_NO AS regionSortNo,\n" +
                "\t\t\tt_sys_company.SORT_NO AS companySortNo,\n" +
                "\t\t\tt_sys_region.COMPANY_ID AS companyId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user_2_region\n" +
                "\t\tLEFT JOIN t_sys_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_region.COMPANY_ID\n" +
                "\t\tLEFT JOIN t_sys_user ON t_sys_user.ID = t_sys_user_2_region.USER_ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_region.PARENT_ID = '" + regionId + "'\n" +
                "\t\tAND t_sys_user_2_region.USER_ID IN (" + senderIds + ")\n" +
                "\t) AS sysRegion  ON region.regionId = sysRegion.regionId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_email.CREATE_USER_ID AS senderId,\n" +
                "\t\tt_sys_email_receiver.USER_ID AS receiverId,\n" +
                "\t\tt_wrt_main.ID AS id,\n" +
                "\t\tt_sys_email_receiver.READER,\n" +
                "\t\tt_wrt_main.TITLE,\n" +
                "\t\tt_sys_email.ID AS emailId,\n" +
                "\t\tt_sys_email.DATA_ID AS WId\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tLEFT JOIN t_sys_email ON t_sys_email_receiver.EMAIL_ID = t_sys_email.ID\n" +
                "\tLEFT JOIN t_wrt_main ON t_wrt_main.ID = t_sys_email.DATA_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_wrt_main.flage !=- 1\n" +
                "\tAND t_sys_email.E_TYPE = 1\n" +
                "\tAND t_sys_email_receiver.USER_ID IN (" + receiverIds + ")\n" +
                "\tAND t_sys_email.CREATE_USER_ID IN (" + senderIds + ")\n" +
                "\tAND t_sys_email.REPORT_DATE = '" + dateTime + "'\n" +
                ") AS email ON email.senderId = sysRegion.userId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tCOUNT(1) AS replyNum,\n" +
                "\t\tDATA_ID\n" +
                "\tFROM\n" +
                "\t\tt_sys_email_receiver\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND REPLY_TIME IS NOT NULL\n" +
                "\tAND DATA_ID IN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_wrt_main.ID\n" +
                "\t\tFROM\n" +
                "\t\t\tt_wrt_main\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_wrt_main.USER_ID IN (" + senderIds + ")\n" +
                "\t\t AND t_sys_email_receiver.USER_ID IN (" + receiverIds + ") \n" +
                "\t\tAND t_wrt_main.REPORT_DATE = '" + dateTime + "'\n" +
                "\t)\n" +
                "\tGROUP BY\n" +
                "\t\tDATA_ID\n" +
                ") AS emailNum ON email.WId = emailNum.DATA_ID\n" +
                "LEFT JOIN t_sys_leave ON sysRegion.userId = t_sys_leave.USER_ID\n" +
                "AND LEAVE_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_sign_in ON sysRegion.userId = t_sys_sign_in.USER_ID\n" +
                "AND SIGN_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID = email.receiverId\n" +
                "ORDER BY region.companySortNo,region.regionSortNo,email.receiverId,sysRegion.userId\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getUserIdByRoleNameAndCompanyId(String companyId, StringBuffer condition, String dateTime, boolean flag) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tv_sys_user.id as userId\n" +
                "FROM\n" +
                "\tv_sys_user\n" +
                "LEFT JOIN t_sys_user_leave ON t_sys_user_leave.USER_ID = v_sys_user.id\n" +
                "WHERE\n" +
                "\t1 = 1\n");
        sql.append(condition);
        sql.append("AND v_sys_user.companyId ='" + companyId + "'\n" +
                "AND (\n" +
                "\tv_sys_user.jobState != 1\n");
        if (flag) {
            sql.append("\tOR t_sys_user_leave.LEAVE_DATE = '" + dateTime + "'\n");
        }
        sql.append(")\n" +
                "GROUP BY\n" +
                "\tv_sys_user.id\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getTodayPlanByUserIdAndDateTimeForGroup(String userIds, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tsysUser.userName,\n" +
                "\tT_WRT_CUSTOMER_VIST_PLAN.CUSTOMER_ADDRESS AS customerAddress,\n" +
                "\tT_WRT_CUSTOMER_VIST_PLAN.ENTOURAGE AS entourage,\n" +
                "\tT_WRT_CUSTOMER_VIST_PLAN.VISIT_GOAL AS visitGoal,\n" +
                "\tT_WRT_CUSTOMER_VIST_PLAN.CUSTOMER_NAME AS customerName,\n" +
                "\tT_WRT_CUSTOMER_VIST_PLAN.COUNTY AS county,\n" +
                "\tCASE\n" +
                "WHEN t_sys_sign_in.ID IS NOT NULL THEN\n" +
                "\t1\n" +
                "WHEN t_sys_leave.ID IS NOT NULL THEN\n" +
                "\t0\n" +
                "WHEN t_sys_email.ID IS NOT NULL THEN\n" +
                "\t3\n" +
                "ELSE\n" +
                "\t2\n" +
                "END AS signState,\n" +
                "CASE WHEN t_sys_email.ID IS NOT NULL THEN\n" +
                "\t1\n" +
                "ELSE\n" +
                "0\n" +
                "END as reportState,\n" +
                " CASE\n" +
                "WHEN t_sys_sign_in.ID IS NOT NULL THEN\n" +
                "\t10\n" +
                "WHEN t_sys_leave.ID IS NOT NULL THEN\n" +
                "\t20\n" +
                "WHEN t_sys_email.ID IS NOT NULL THEN\n" +
                "\t30\n" +
                "ELSE\n" +
                "\t40\n" +
                "END signSort\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user.ID AS userId,\n" +
                "\t\t\tt_sys_user.`NAME` AS userName,\n" +
                "\t\t\tt_sys_depart.`NAME` AS departName,\n" +
                "\t\t\tt_sys_company.`NAME` AS companyName,\n" +
                "\t\t\tt_sys_company.SORT_NO AS companySortNo,\n" +
                "\t\t\tt_sys_depart.SORT_NO AS departSortNo\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user\n" +
                "\t\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\t\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_user.ID IN (" + userIds + ")\n" +
                "\t\tAND t_sys_role.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_depart.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t) AS sysUser\n" +
                "LEFT JOIN T_WRT_CUSTOMER_VIST_PLAN ON sysUser.userId = T_WRT_CUSTOMER_VIST_PLAN.CREATE_USER_ID\n" +
                "AND T_WRT_CUSTOMER_VIST_PLAN.CREATE_USER_ID IN (" + userIds + ")\n" +
                "AND T_WRT_CUSTOMER_VIST_PLAN.REPORT_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_sign_in ON t_sys_sign_in.USER_ID = sysUser.userId\n" +
                "AND t_sys_sign_in.SIGN_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_leave ON t_sys_leave.USER_ID = sysUser.userId\n" +
                "AND t_sys_leave.LEAVE_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_email ON t_sys_email.CREATE_USER_ID = sysUser.userId\n" +
                "AND t_sys_email.REPORT_DATE = '" + dateTime + "'\n" +
                "AND t_sys_email.E_TYPE = 1\n" +
                "ORDER BY\n" +
                "\tsysUser.companySortNo,\n" +
                "\tsysUser.departSortNo,\n" +
                "\tsignSort,\n" +
                "sysUser.userId\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getFirstVisitAndSignForToday(String userId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tsysUser.*,\n" +
                "\tt_sys_sign_in.LATITUDE AS signInLatitude,\n" +
                "\tt_sys_sign_in.LONGITUDE AS signInLongitude,\n" +
                "\tt_sys_sign_in.SIGN_IN_SPEED AS signInspeed,\n" +
                "\tt_sys_sign_in.CREATE_TIME as signTime,\n" +
                "\tt_sys_sign_in.SIGN_IN_TIME_DIFF AS signInDiff,\n" +
                "\tt_sys_sign_in.SIGN_IN_DISTANCE AS signInDistance,\n" +
                "\twrtArr.arrivalsTime,\n" +
                "\twrtArr.arrivalsLatitude,\n" +
                "\twrtArr.arrivalsLongitude\n" +
                "FROM\n" +
                "\t(SELECT\n" +
                "\t\t\tv_sys_user.companySortNo,\n" +
                "\t\t\tv_sys_user.departSortNo,\n" +
                "\t\t\tv_sys_user.roleNameLevel,\n" +
                "\t\t\tv_sys_user.`name` AS userName,\n" +
                "\t\t\tv_sys_user.`id` AS userId," +
                "\t\tv_sys_user.departName ,\n" +
                "\t\t\tv_sys_user.roleName ,\n" +
                "\t\t\tv_sys_user.companyName \n" +
                "\t\tFROM\n" +
                "\t\t\tv_sys_user\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND v_sys_user.ID  IN (" + userId + ")\n" +
                "\t) AS sysUser\n" +
                "LEFT JOIN t_sys_sign_in ON t_sys_sign_in.CREATE_USER_ID = sysUser.userId\n" +
                "AND t_sys_sign_in.SIGN_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID AS userId,\n" +
                "\t\tmin(\n" +
                "\t\t\tt_wrt_customer_visit.ARRIVALS_TIME\n" +
                "\t\t) AS arrivalsTime,\n" +
                "\t\tt_wrt_customer_visit.ARRIVALS_LATITUDE AS arrivalsLatitude,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_LONGITUDE AS arrivalsLongitude\n" +
                "\tFROM\n" +
                "\t\tt_wrt_customer_visit\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_wrt_customer_visit.VISIT_MODE = 1\n" +
                "\tAND t_wrt_customer_visit.REPORT_DATE = '" + dateTime + "'\n" +
                "\tGROUP BY\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID\n" +
                ") AS wrtArr ON wrtArr.userId = sysUser.userId\n" +
                "GROUP BY sysUser.userId,sysUser.roleName\n" +
                "ORDER BY \tsysUser.companySortNo,\n" +
                "\tsysUser.departSortNo,\n" +
                "\tsysUser.roleNameLevel,\n" +
                "\tsysUser.userName\n");
        return super.queryForJsonList(sql.toString());
    }

    //
    public List<JSONObject> getForPersonalDayByUserIdAndDateTime(String userIds, String dateTime, Integer states) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tsysUser.*, ifnull(faceCustomer.faceNum, 0) AS faceNum,\n" +
                "\tifnull(telCustomer.telNum, 0) AS telNum,\n" +
                "\tCASE\n" +
                "WHEN t_sys_sign_in.ID IS NOT NULL THEN\n" +
                "\t0\n" +
                "WHEN t_sys_leave.ID IS NOT NULL THEN\n" +
                "\t1\n" +
                "ELSE\n" +
                "\t2\n" +
                "END AS signState\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user.residence AS residence,\n" +
                "\t\t\tt_sys_user. NAME AS userName,\n" +
                "\t\t\tt_sys_user.id AS userId,\n" +
                "\t\t\tt_sys_depart. NAME AS departName,\n" +
                "\t\t\tt_sys_role_name. NAME AS roleName,\n" +
                "\t\t\tt_sys_company. NAME AS companyName,\n" +
                "\t\t\tt_sys_company.sort_no AS companySortNo,\n" +
                "\t\t\tt_sys_depart.sort_no AS departSortNo,\n" +
                "\t\t\tt_sys_role_name.sort_no AS roleNameLevel\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user\n" +
                "\t\tLEFT JOIN t_sys_role ON t_sys_user.role_id = t_sys_role.id\n" +
                "\t\tLEFT JOIN t_sys_role_name ON t_sys_role.role_name_id = t_sys_role_name.id\n" +
                "\t\tLEFT JOIN t_sys_depart ON t_sys_depart.id = t_sys_role.depart_id\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_company.id = t_sys_depart.company_id\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_sys_company.del_flag = 0\n" +
                "\t\tAND t_sys_depart.del_flag = 0\n" +
                "\t\tAND t_sys_role.del_flag = 0\n" +
                "\t\tAND t_sys_role_name.del_flag = 0\n" +
                "\t\tAND t_sys_user.ID IN (" + userIds + ")\n" +
                "\t\tUNION ALL\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tt_sys_user.residence AS residence,\n" +
                "\t\t\t\tt_sys_user. NAME AS userName,\n" +
                "\t\t\t\tt_sys_user.id AS userId,\n" +
                "\t\t\t\tt_sys_depart. NAME AS departName,\n" +
                "\t\t\t\tt_sys_role_name. NAME AS roleName,\n" +
                "\t\t\t\tt_sys_company. NAME AS companyName,\n" +
                "\t\t\t\tt_sys_company.sort_no AS companySortNo,\n" +
                "\t\t\t\tt_sys_depart.sort_no AS departSortNo,\n" +
                "\t\t\t\tt_sys_role_name.sort_no AS roleNameLevel\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_sys_user\n" +
                "\t\t\tLEFT JOIN t_sys_user_2_role ON t_sys_user.id = t_sys_user_2_role.user_id\n" +
                "\t\t\tLEFT JOIN t_sys_role ON t_sys_user_2_role.role_id = t_sys_role.id\n" +
                "\t\t\tLEFT JOIN t_sys_role_name ON t_sys_role.role_name_id = t_sys_role_name.id\n" +
                "\t\t\tLEFT JOIN t_sys_depart ON t_sys_depart.id = t_sys_role.depart_id\n" +
                "\t\t\tLEFT JOIN t_sys_company ON t_sys_company.id = t_sys_depart.company_id\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\t1 = 1\n" +
                "\t\t\tAND t_sys_company.del_flag = 0\n" +
                "\t\t\tAND t_sys_depart.del_flag = 0\n" +
                "\t\t\tAND t_sys_role.del_flag = 0\n" +
                "\t\t\tAND t_sys_role_name.del_flag = 0\n" +
                "\t\t\tAND t_sys_user.ID IN (" + userIds + ")\n" +
                "\t) AS sysUser\n" +
                "LEFT JOIN t_sys_sign_in ON t_sys_sign_in.USER_ID = sysUser.userId\n" +
                "AND t_sys_sign_in.SIGN_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_leave ON t_sys_leave.USER_ID = sysUser.userId\n" +
                "AND t_sys_leave.LEAVE_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID AS userId,\n" +
                "\t\tCOUNT(1) AS faceNum\n" +
                "\tFROM\n" +
                "\t\tt_wrt_customer_visit\n" +
                "\tLEFT JOIN t_wrt_customer ON t_wrt_customer_visit.CUSTOMER_ID = t_wrt_customer.ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_wrt_customer_visit.VISIT_MODE = 1\n" +
                "\tAND t_wrt_customer_visit.CUSTOMER_ID IS NOT NULL\n" +
                "\tAND t_wrt_customer_visit.REPORT_DATE = '" + dateTime + "'\n" +
                "\tAND t_wrt_customer_visit.CREATE_USER_ID IN (" + userIds + ")\n" +
                "\tAND (\n" +
                "\t\tsubstring(\n" +
                "\t\t\tt_wrt_customer_visit.ARRIVALS_TIME,\n" +
                "\t\t\t11,\n" +
                "\t\t\t3\n" +
                "\t\t) >= 12\n" +
                "\t) = " + states + "\n" +
                "\tGROUP BY\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID\n" +
                ") AS faceCustomer ON sysUser.userId = faceCustomer.userId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID AS userId,\n" +
                "\t\tCOUNT(1) AS telNum\n" +
                "\tFROM\n" +
                "\t\tt_wrt_customer_visit\n" +
                "\tLEFT JOIN t_wrt_customer ON t_wrt_customer_visit.CUSTOMER_ID = t_wrt_customer.ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_wrt_customer_visit.VISIT_MODE = 0\n" +
                "\tAND t_wrt_customer_visit.REPORT_DATE = '" + dateTime + "'\n" +
                "\tAND t_wrt_customer_visit.CREATE_USER_ID IN (" + userIds + ")\n" +
                "\tAND (\n" +
                "\t\tsubstring(\n" +
                "\t\t\tt_wrt_customer_visit.ARRIVALS_TIME,\n" +
                "\t\t\t11,\n" +
                "\t\t\t3\n" +
                "\t\t) >= 12\n" +
                "\t) = " + states + "\n" +
                "\tGROUP BY\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID\n" +
                ") AS telCustomer ON sysUser.userId = telCustomer.userId\n" +
                "GROUP BY\n" +
                "\tsysUser.userId,\n" +
                "\tsysUser.roleName\n" +
                "ORDER BY\n" +
                "\tsysUser.companySortNo,\n" +
                "\tsysUser.departSortNo,\n" +
                "\tsysUser.roleNameLevel,\n" +
                "\tsysUser.userName");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getSignForRegionList(String companyId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_sys_region.ID AS regionId,\n" +
                "\tt_sys_region.`NAME` AS regionName\n" +
                "FROM\n" +
                "\tt_sys_region\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_region.COMPANY_ID = '" + companyId + "'\n" +
                "AND t_sys_region.PARENT_ID = '0'\n" +
                "AND t_sys_region.DEL_FLAG = '0'\n" +
                "ORDER BY\n" +
                "\tt_sys_region.SORT_NO");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getSignForRegion(String companyId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tregion.regionName,\n" +
                "\tregion.bigRegionId,\n" +
                "\tmanager.managerId,\n" +
                "\tmanager.managerName,\n" +
                "\tsign_leave.createTime,\n" +
                "\tIFNULL(sign_leave.signState, 2) AS signState,\n" +
                "\tsign_leave.address\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_region.ID AS regionId,\n" +
                "\t\t\tt_sys_region.`NAME` AS regionName,\n" +
                "\t\t\tt_sys_region.SORT_NO AS sortNo,\n" +
                "\t\t\tbigRegion.SORT_NO AS bigSortNo,\n" +
                "\t\t\tbigRegion.ID AS bigRegionId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_region\n" +
                "\t\tLEFT JOIN t_sys_region AS bigRegion ON t_sys_region.PARENT_ID = bigRegion.ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_sys_region.COMPANY_ID = '" + companyId + "'\n" +
                "\t\tAND t_sys_region.PARENT_ID != '0'\n" +
                "\t\tAND t_sys_region.DEL_FLAG = '0'\n" +
                "\t) AS region\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_user.ID AS managerId,\n" +
                "\t\tt_sys_user.`NAME` AS managerName,\n" +
                "\t\tt_sys_user_2_region.REGION_ID AS regionId\n" +
                "\tFROM\n" +
                "\t\tt_sys_user\n" +
                "\tLEFT JOIN t_sys_user_2_region ON t_sys_user.ID = t_sys_user_2_region.USER_ID\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE\n" +
                "\t\t(t_sys_role_name.`NAME` = '区域经理' OR t_sys_role_name.`NAME` = '区域组长')\n" +
                "\tAND t_sys_user.JOB_STATE != 1\n" +
                ") AS manager ON manager.regionId = region.regionId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_user.ID AS managerId,\n" +
                "\t\tt_sys_user.`NAME` AS managerName,\n" +
                "\t\tt_sys_user_2_region.REGION_ID AS regionId,\n" +
                "\t\tt_sys_sign_in.CREATE_TIME AS createTime,\n" +
                "\t\tt_sys_sign_in.ADDRESS AS address,\n" +
                "\t\t'0' AS signState\n" +
                "\tFROM\n" +
                "\t\tt_sys_sign_in\n" +
                "\tLEFT JOIN t_sys_user_2_region ON t_sys_sign_in.USER_ID = t_sys_user_2_region.USER_ID\n" +
                "\tLEFT JOIN t_sys_user ON t_sys_sign_in.USER_ID = t_sys_user.ID\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE\n" +
                "\t\tt_sys_sign_in.SIGN_DATE = '" + dateTime + "'\n" +
                "\tAND (t_sys_role_name.`NAME` = '区域经理' OR t_sys_role_name.`NAME` = '区域组长')\n" +
                "\tUNION ALL\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user.ID AS managerId,\n" +
                "\t\t\tt_sys_user.`NAME` AS managerName,\n" +
                "\t\t\tt_sys_user_2_region.REGION_ID AS regionId,\n" +
                "\t\t\tt_sys_leave.CREATE_TIME AS createTime,\n" +
                "\t\t\tt_sys_leave.REMARK AS address,\n" +
                "\t\t\t'1' AS signState\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_leave\n" +
                "\t\tLEFT JOIN t_sys_user_2_region ON t_sys_leave.USER_ID = t_sys_user_2_region.USER_ID\n" +
                "\t\tLEFT JOIN t_sys_user ON t_sys_leave.USER_ID = t_sys_user.ID\n" +
                "\t\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\t\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_leave.LEAVE_DATE = '" + dateTime + "'\n" +
                "\t\tAND (t_sys_role_name.`NAME` = '区域经理' OR t_sys_role_name.`NAME` = '区域组长')\n" +
                ") AS sign_leave ON region.regionId = sign_leave.regionId\n" +
                "ORDER BY\n" +
                "\tregion.bigSortNo,\n" +
                "\tregion.sortNo");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getForPersonalCostByUserIdAndDateTime(String userIds, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tv_sys_user.ID AS userId,\n" +
                "\tv_sys_user.`NAME` AS userName,\n" +
                "\t\t\tv_sys_user.departName,\n" +
                "\t\t\tv_sys_user.roleName,\n" +
                "\t\t\tv_sys_user.companyName,\n" +
                "\tt_wrt_cost.CREATE_USER_ID AS createUserId,\n" +
                "\tt_wrt_cost.CREATE_TIME AS createTime,\n" +
                "\tt_wrt_cost.COST_SMALL_NAME AS costSmallName,\n" +
                "\tt_wrt_cost.INVOICE_FLAG AS invoiceFlag,\n" +
                "\tt_wrt_cost.`START` AS bxState,\n" +
                "\tt_wrt_cost.REPORT_DATE AS date,\n" +
                "\tIFNULL(`t_wrt_cost`.`MONEY`, 0) AS `money`,\n" +
                "\tt_wrt_cost_detail.MILEAGE AS mileage,\n" +
                "\tt_wrt_cost_detail.IMG AS img,\n" +
                "\tt_wrt_cost_detail.LICENSE AS license,\n" +
                "\tt_wrt_cost_detail.HOTEL_NAME AS hotelName,\n" +
                "\tt_wrt_cost_detail.COHABITATION AS cohabitation,\n" +
                "\tt_wrt_cost_detail.HOTEL_TEL AS hotelTel,\n" +
                "\tt_wrt_cost_detail.RESIDENCE AS residence,\n" +
                "\tt_wrt_cost_detail.`HOST` AS hostUser,\n" +
                "\tt_wrt_cost_detail.GUESTS AS guests,\n" +
                "\tt_wrt_cost_detail.GUEST_NATURE AS guestNature,\n" +
                "\tt_wrt_cost_detail.RESTAURANT_NAME AS restaurantName,\n" +
                "\tt_wrt_cost_detail.RESTAURANT_TEL AS restaurantTel,\n" +
                "\tt_wrt_cost_detail.ENTERTAINMENT_VENUE AS entertainmentVenue,\n" +
                "\tt_wrt_cost_detail.PHONE AS phone,\n" +
                "\tt_wrt_cost_detail.BEFORE_OFFICE AS beforeOffice,\n" +
                "\tt_wrt_cost_detail.AFTER_OFFICE AS afterOffice,\n" +
                "\tt_wrt_cost_detail.OFFICE_MATTERS AS officemetters,\n" +
                "\tt_wrt_cost_detail.PREDICT_MONEY AS predictMoney,\n" +
                "\tt_wrt_cost.REMARK AS remark\n" +
                "FROM\n" +
                "\tt_wrt_cost\n" +
                "LEFT JOIN t_wrt_cost_detail ON t_wrt_cost.ID = t_wrt_cost_detail.FK_COST_ID\n" +
                "LEFT JOIN v_sys_user ON v_sys_user.ID = t_wrt_cost.CREATE_USER_ID\n" +
                "WHERE\n" +
                "\tt_wrt_cost.CREATE_USER_ID IN (" + userIds + ")\n" +
                "AND v_sys_user.jobState != 1\n" +
                "AND v_sys_user.main = 1\n" +
                "AND t_wrt_cost.REPORT_DATE = '" + dateTime + "'\n" +
                "ORDER BY\n" +
                "\tt_wrt_cost.CREATE_TIME\n");
        return queryForJsonList(sql.toString());
    }

    public List<JSONObject> getNoUserRegionReader(String regionId, String roleNames, String companyId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tv_sys_user.id as userId,\n" +
                "\tv_sys_user.`name` as userName,\n" +
                "\tv_sys_user.roleName,\n" +
                "\tv_sys_user.companyId,\n" +
                "\tv_sys_user.roleNameLevel,\n" +
                "v_sys_user.main\n" +
                "FROM\n" +
                "\tv_sys_user\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID = v_sys_user.id\n" +
                "LEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID = t_sys_region.id\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_region.DEL_FLAG = 0\n" +
                "AND v_sys_user.jobState != 1\n" +
                "AND v_sys_user.companyId = '" + companyId + "'\n" +
                "AND t_sys_user_2_region.REGION_ID = '" + regionId + "'\n" +
                "AND v_sys_user.roleName IN (" + roleNames + ")\n" +
                "ORDER BY roleNameLevel desc,main desc\n");
        return super.queryForJsonList(sql.toString());
    }

    public Integer getSendEmail(String userId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tcount(1) AS count\n" +
                "FROM\n" +
                "\tt_sys_email\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND REPORT_DATE = '" + dateTime + "'\n" +
                "AND t_sys_email.CREATE_USER_ID = '" + userId + "'\n" +
                "AND t_sys_email.E_TYPE = 1\n");
        String count = super.queryForString(sql.toString(), new Object[]{});
        return Integer.parseInt(count);
    }
}